<?php

namespace addons\xpframework\library\xcore\xcore\utils;

use addons\xpframework\library\xcore\xcore\XcException;
use fast\Random;
use think\Db;


/**
 * Docs
 * https://www.kancloud.cn/manual/thinkphp5/118059
 */
class XcDatabaseUtils
{
    /**
     * existsTablefield("fa_test", "id")
     * @return boolaen true
     */
    public static function existsTablefield($tablename, $fieldcode)
    {
        $fields = static::queryTablefieldList($tablename);
        foreach ($fields as $field) {
            if (strtolower($field['name']) == strtolower($fieldcode)) {
                return true;
            }
        }
        return false;
    }


    /**
     * existsTable("fa_test")
     * @return boolaen true
     */
    public static function existsTable($tablename)
    {
        $tables = static::queryTableList();
        foreach ($tables as $table) {
            if (strtolower($table['name']) == strtolower($tablename)) {
                return true;
            }
        }
        return false;
    }

    /**
     * alertTableField("alterType", "fa_test", "field01", "varchar(255)", "字段01");
     * @return void
     */
    public static function alertTableField($alterType, $tablename, $fieldcode, $columnType, $fieldcomment)
    {
        $publishfieldcode = $fieldcode;

        $sql = null;
        if (!empty($fieldcode)) {
            static::assertValidateFieldCode($fieldcode);

            $fieldcode = strtolower($fieldcode);
            switch ($alterType) {
                case "add_column":
                    $sql = "ALTER TABLE {$tablename} ADD COLUMN {$fieldcode} {$columnType} COMMENT '{$fieldcomment}';";
                    break;
                case "change_column":
                    $sql = "ALTER TABLE {$tablename} CHANGE {$fieldcode} {$fieldcode} {$columnType} COMMENT '{$fieldcomment}';";
                    break;
                case "drop_column":
                    // $sql = "ALTER TABLE {$tablename} DROP COLUMN {$fieldcode} {$columnType} COMMENT '{$fieldcomment}';";
                    $rr = Random::alpha(3);
                    $publishfieldcode = "deleted_{$fieldcode}_{$rr}";
                    $sql = "ALTER TABLE {$tablename} CHANGE {$fieldcode} {$publishfieldcode} {$columnType};";
                    break;
            }
            Db::execute($sql);
        }
        return $publishfieldcode;
    }

    /**
     * appendTablePrefix("test");
     * @return string fa_test
     */
    public static function appendTablePrefix($withoutPrefixTablename)
    {
        $prefix = XcConfigUtils::databaseconfig("prefix");
        return $prefix . $withoutPrefixTablename;
    }

    /**
     * 前台页面下拉框使用方式:
     * 1. control增加如下代码:
            public function selectTables()
            {
                $displayname = XcRequestUtils::get("displayname");
                $list = XcDatabaseUtils::queryTableList($displayname);
                return json(['list' => $list, 'total' => count($list)]);
            }
     * 2. html代码
            <input id="c-selectmdmtablename" class="form-control selectpage" name="row[selectmdmtablename]" type="text" value="{$row.selectmdmtablename|htmlentities}"
                data-source="csmitsm/xcflow/selectTables" data-primary-key="name" data-field="displayname" > 
     * 
     * queryTableList("test", "fa_test")
     * @return array 
     */
    public static function queryTableList($filterString = null, $excludeTables = [])
    {
        $list = [];

        $tt = Db::query("SHOW TABLE STATUS");
        foreach ($tt as $item) {
            $tablename = strtolower($item['Name']);
            $comment = $item["Comment"];

            if (!in_array($item['Name'], $excludeTables)) {
                // filter by filterString
                if (!empty($filterString) && stripos($tablename, $filterString) == false && stripos($comment, $filterString) == false) {
                    continue;
                }
                $list[] = [
                    "name" => $tablename,
                    "comment" => $comment,
                    "displayname" => $tablename . ' ' . $comment,
                ];
            }
        }
        return $list;
    }

    /**
     * getTableInfo("fa_test");
     *
     * @return array
     */
    public static function getTableInfo($tablename)
    {
        $tablename = static::getFactTablename($tablename);
        $tt = Db::query("SHOW TABLE STATUS where name=?", [$tablename]);
        foreach ($tt as $item) {
            $tablename = strtolower($item['Name']);
            $comment = $item["Comment"];
            return [
                "name" => $tablename,
                "comment" => $comment,
                "displayname" => $tablename . ' ' . $comment,
            ];
        }
        throw new XcException("数据异常", 0, null, "数据库中无此表:{$tablename}");
    }

    /**
     * 前台页面下拉框使用方式:
     * 1. control增加如下代码:
            public function selectTablefields()
            {
                $displayname = XcRequestUtils::get("displayname");
                $tablenameParam = XcRequestUtils::getBuildCustomParams("tablename");
                $list = XcDatabaseUtils::queryTablefieldList($tablenameParam[2], $displayname);
                return json(['list' => $list, 'total' => count($list)]);
            }
     * 2. html代码
            todo
     * 
     * queryTablefieldList("fa_test", "title");
     * @return array ["id", "createtime", "status"]
     */
    public static function queryTablefieldList($tablename, $filterString = null)
    {
        $list = [];

        $tt = XcDatabaseUtils::getListBySql("information_schema.columns", [
            ["TABLE_SCHEMA", "=", XcConfigUtils::databaseconfig("database")],
            ["TABLE_NAME", "=", XcDatabaseUtils::getFactTablename($tablename)]
        ], null, null, false);

        // $database = XcConfigUtils::databaseconfig("database");
        // $tt = Db::query("SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA=? and TABLE_NAME=?", [$database, $tablename]);

        foreach ($tt as $item) {
            $comment = null;
            $pos = strpos($item['COLUMN_COMMENT'], ':');
            if ($pos !== false) {
                // 冒号存在，截取冒号之前的字符串
                $comment = substr($item['COLUMN_COMMENT'], 0, $pos);
            } else {
                // 冒号不存在，返回整个字符串
                $comment = $item['COLUMN_COMMENT'];
            }

            $fieldname = strtolower($item['COLUMN_NAME']);
            $fieldtype = strtolower($item['DATA_TYPE']);

            // filter by filterString
            if (!empty($filterString) && stripos($fieldname, $filterString) == false && stripos($comment, $filterString) == false) {
                continue;
            }

            $list[] = [
                "name" => $fieldname,
                "comment" => $comment,
                "displayname" => $fieldname,
                "fieldtype" => $fieldtype,
                // "displayname" => $fieldname . ' ' . $comment,
            ];
        }
        return $list;
    }


    /**
     * _queryTabledataList("fa_test","title,content","title,content","test",1,20);
     * @return array
     */
    public static function queryTabledataList($tablename, $showfields, $filterfields, $filtervalue, $pageno, $pagesize, $whereFieldValues = [], $orders = null, $hasNormalField = true)
    {
        $tablename = str_replace("__PREFIX__", XcConfigUtils::databaseconfig("prefix"), $tablename);

        $sqlcount = DB::table($tablename);
        $sql =  DB::table($tablename);
        if (!empty($showfields)) {
            $sql->field($showfields);
        }
        if (!empty($filterfields)) {
            $filterfields = str_replace(",", "|", $filterfields);
            $sql->whereLike($filterfields, "%{$filtervalue}%");
            $sqlcount->whereLike($filterfields, "%{$filtervalue}%");
        }

        foreach ($whereFieldValues as $item) {
            if ($item == null) {
                continue;
            }
            if (count($item) == 2) {
                $sqlcount->where($item[0], $item[1]);
                $sql->where($item[0], $item[1]);
            } else {
                $sqlcount->where($item[0], $item[1], $item[2]);
                $sql->where($item[0], $item[1], $item[2]);
            }
        }
        if ($hasNormalField === true) {
            $sqlcount->where('status', 'in', ['normal', 'draft']);
            $sql->where('status', 'in', ['normal', 'draft']);
        }
        if ($orders != null) {
            $sql->order($orders);
        }


        $total = $sqlcount->count();
        $list = $sql->page($pageno, $pagesize)->select();
        return [
            "list" => $list,
            "total" => $total,
            "totalpage" => intval($total / $pagesize) + 1
        ];
    }


    /**
     * validateFieldCode("title");
     *
     * @return boolean
     */
    public static function assertValidateFieldCode($fieldcode)
    {
        // 检查是否为空
        if (empty($fieldcode)) {
            XcResponseUtils::error("不能为空:" . $fieldcode);
        }

        // 检查是否包含特殊字符
        if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $fieldcode)) {
            XcResponseUtils::error("包含特殊字符:" . $fieldcode);
        }

        // 检查是否使用了MySQL的保留关键字
        // 这里只是示例，实际上MySQL的保留关键字非常多，这里只列出了几个常见的
        $reservedKeywords = [
            'SELECT',
            'FROM',
            'WHERE',
            'AND',
            'OR',
            'NOT',
            'IN',
            'IS',
            'NULL',
            'LIKE',
            'ORDER',
            'GROUP',
            'BY',
            'HAVING',
            'LIMIT',
            'AS',
            'ALL',
            'ANY',
            'SOME',
            'EXISTS',
            'ASC',
            'DESC',
            'DISTINCT',
            'UNION',
            'CASE',
            'WHEN',
            'THEN',
            'END'
        ];
        if (in_array(strtoupper($fieldcode), $reservedKeywords)) {
            XcResponseUtils::error("无法使用MySQL的保留关键字:" . $fieldcode);
        }
    }

    /**
     * getFactTablename("__PREFIX__admin");  -> fa_admin
     * @return string
     */
    public static function getFactTablename($abstractTablename)
    {
        $prefix = XcConfigUtils::databaseconfig("prefix");
        $tablename = str_replace("__PREFIX__", $prefix, $abstractTablename);

        return $tablename;
    }

    public static function getAbstraceTablename($factTablename)
    {
        $prefix = XcConfigUtils::databaseconfig("prefix");
        if (strpos($factTablename, $prefix) === 0) {
            return '__PREFIX__' . substr($factTablename, strlen($prefix));
        } else {
            return $factTablename;
        }
    }

    public static function getNoprefixTablename($tablename)
    {
        $prefix = XcConfigUtils::databaseconfig("prefix");
        if (strpos($tablename, $prefix) === 0) {
            return substr($tablename, strlen($prefix));
        }
        $prefix = "__PREFIX__";
        if (strpos($tablename, $prefix) === 0) {
            return substr($tablename, strlen($prefix));
        }
        return $tablename;
    }

    public static function getRowById($tablename, $id, $hasNormalField = false, $isAssert = true, $fields = "*")
    {
        $tablename = static::getFactTablename($tablename);
        $dao = Db::table($tablename);

        return XcDaoUtils::getRowById($dao, $id, $hasNormalField, $isAssert, $fields);
    }


    public static function getRowBySql($tablename, $whereFieldValues, $fieldString = null, $hasNormalField = true, $isAssert = false, $orders = "id desc")
    {
        $tablename = static::getFactTablename($tablename);
        $dao = Db::table($tablename);
        return XcDaoUtils::getRowBySql($dao, $whereFieldValues, $fieldString, $hasNormalField, $isAssert, $orders);
    }

    public static function getListBySql($tablename, $whereFieldValues, $fieldString = null, $orders = null, $hasNormalField = true)
    {
        // replace __PREFIX__
        $tablename = static::getFactTablename($tablename);
        $dao = Db::table($tablename);
        return XcDaoUtils::getListBySql($dao, $whereFieldValues, $fieldString, $orders, $hasNormalField);
    }

    public static function getPageListBySql($tablename, $whereFieldValues, $offset, $limit, $fieldString = null, $orders = null, $hasNormalField = true)
    {
        // replace __PREFIX__$
        $tablename = static::getFactTablename($tablename);
        $dao = Db::table($tablename);
        return XcDaoUtils::getPageListBySql($dao, $whereFieldValues, $offset, $limit, $fieldString, $orders, $hasNormalField);
    }

    public static function bindDbListColumn(&$list, $key, $tablename, $daoalias, $daofieldnames)
    {
        // replace __PREFIX__
        $tablename = static::getFactTablename($tablename);
        $dao = Db::table($tablename);
        return XcDaoUtils::bindDbListColumn($list, $key, $dao, $daoalias, $daofieldnames);
    }

    public static function bindDbListMultiColumn(&$list, $key, $tablename, $daoalias, $daofieldname)
    {
        // replace __PREFIX__
        $tablename = static::getFactTablename($tablename);
        $dao = Db::table($tablename);
        return XcDaoUtils::bindDbListMultiColumn($list, $key, $dao, $daoalias, $daofieldname);
    }

    public static function save($tablename, $id, $data)
    {
        $tablename = static::getFactTablename($tablename);
        Db::table($tablename)->where('id', $id)->update($data);

        return static::getRowById($tablename, $id);
    }

    public static function create($tablename, $data)
    {
        $tablename = static::getFactTablename($tablename);
        Db::table($tablename)->insert($data);
        $id = Db::name($tablename)->getLastInsID();
        return static::getRowById($tablename, $id);
    }

    public static function delete($tablename, $id)
    {
        static::save($tablename, $id, ['status' => "hidden"]);
    }

    public static function deleteRows($tablename, $wheres)
    {
        $list = static::getListBySql($tablename, $wheres);
        foreach ($list as $item) {
            static::delete($tablename, $item['id']);
        }
    }

    /**
     * @return array
     */
    public static function createOrUpdateOrDeleteDatas($tablename, $datas, $pkFields, $writeinDataidFieldname = null, $dataFields = null, $whereFieldValues = [], $hasNormalField = true, $isLogicDelete = true)
    {
        $tablename = static::getFactTablename($tablename);
        $list = static::getListBySql($tablename, $whereFieldValues, null, null, $hasNormalField);
        // create or update
        foreach ($datas as &$data) {
            $isFound = false;
            foreach ($list as $row) {
                $isEqual = true;
                foreach ($pkFields as $pkField) {
                    if ($data[$pkField] != $row[$pkField]) {
                        $isEqual = false;
                    }
                }
                if ($isEqual === true) {
                    $isFound = true;
                    break;
                }
            }

            $toSaveModel = [];
            if ($dataFields == null) {
                $toSaveModel = $data;
            } else {
                foreach ($dataFields as $dataField) {
                    $toSaveModel[$dataField] = $data[$dataField];
                }
            }

            if ($isFound === false) {
                $row = static::create($tablename, $toSaveModel);
            } else {
                $row->save($toSaveModel);
            }

            if ($writeinDataidFieldname != null) {
                $data[$writeinDataidFieldname] = $row['id'];
            }
        }

        // delete
        foreach ($list as $row) {
            $isFound = false;
            foreach ($datas as $data) {
                $isEqual = true;
                foreach ($pkFields as $pkField) {
                    if ($data[$pkField] != $row[$pkField]) {
                        $isEqual = false;
                    }
                }
                if ($isEqual === true) {
                    $isFound = true;
                    break;
                }
            }
            if ($isFound === false) {
                if ($isLogicDelete === true) {
                    $row->save(['status' => 'hidden']);
                } else {
                    $row->delete();
                }
            }
        }

        return $datas;
    }

    /**
     * getListByPuresql("select t.* from fa_test");
     * @return array
     */
    public static function getListByPuresql($sql)
    {

        $replace = XcConfigUtils::databaseconfig("prefix");
        $search = "__prefix__";
        $sql = preg_replace("/" . preg_quote($search, "/") . "/i", $replace, $sql);

        return Db::query($sql);
    }
}
